﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

using ZhangBY.Data;
using ZhangBY.Entity;
using ZhangBY.Common;
using ZhangBY.Data.IDbSupplier;

namespace ZhangBY.Data.SqlServer
{
    public partial class DataProvider : IDataSupplier
    {
        
        /// <summary>
        /// 根據用戶ID查找資源模塊
        /// 返回【ACCOUNT】、【MODULENAME】字段
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public DataSet GetCompanyModulesByCompanyUserId(string companyUserid)
        {
            DbParameter getPar = DBHelper.CreateInParam("@id", (DbType)SqlDbType.Int, companyUserid);
            string commandText = "";
            commandText += "SELECT [dbo].[zby_CA_CompanyUser].[Account], [dbo].[zby_CA_CompanyModule].[Name] AS [CompanyModuleName]";
            commandText += "FROM [dbo].[zby_CA_CompanyRoles] INNER JOIN [dbo].[zby_CA_CompanyRolesCompanyModule] ON ";
            commandText += "[dbo].[zby_CA_CompanyRoles].[ID] = [dbo].[zby_CA_CompanyRolesCompanyModule].[RolesID] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyModule] ON [dbo].[zby_CA_CompanyRolesCompanyModule].[CompanyModuleID] = [dbo].[zby_CA_CompanyModule].[ID] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyUserRoles] ON [dbo].[zby_CA_CompanyRoles].[ID] = [dbo].[zby_CA_CompanyUserRoles].[CompanyRolesId] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyUser] ON [dbo].[zby_CA_CompanyUserRoles].[CompanyUserId] = [dbo].[zby_CA_CompanyUser].[ID]";
            commandText += "WHERE ([dbo].[zby_CA_CompanyUser].[ID] = @id)";
            commandText += "GROUP BY [dbo].[zby_CA_CompanyUser].[Account], [dbo].[zby_CA_CompanyModule].[Name]";

            return DBHelper.ExecuteDataSet(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據模塊ID取得菜單
        /// </summary>
        /// <param name="moduleId"></param>
        /// <returns></returns>
        public DataSet GetMenuByCompanyModuleId(string moduleId)
        {
            DbParameter getPar = DBHelper.CreateInParam("@moduleid", (DbType)SqlDbType.Int, int.MaxValue, moduleId);
            string commandText = "SELECT * FROM [dbo].[zby_CA_CompanyMenu]WHERE [dbo].[zby_CA_CompanyMenu].[CompanyModuleID]=@moduleid";
            return DBHelper.ExecuteDataSet(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據PID取得菜單
        /// </summary>
        /// <param name="moduleId"></param>
        /// <returns></returns>
        public DataSet GetCompanyMenuByPId(string pid)
        {
            DbParameter getPar = DBHelper.CreateInParam("@pid", (DbType)SqlDbType.Int, int.MaxValue, pid);
            string commandText = "SELECT * FROM [dbo].[zby_CA_CompanyMenu]WHERE [dbo].[zby_CA_CompanyMenu].[PID]=@pid ORDER BY [MenuOrder] ASC";
            return DBHelper.ExecuteDataSet(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據全部模塊信息列表
        /// </summary>
        /// <param name="moduleId"></param>
        /// <returns></returns>
        public DataTable GetAllCompanyModuleInfo()
        {
            return DBHelper.ExecuteDataSet(CommandType.Text, "SELECT * FROM [dbo].[zby_CA_CompanyModule]").Tables[0];
        }
        /// <summary>
        /// 根據用戶ID取得菜單信息
        /// </summary>
        /// <param name="companyUserId"></param>
        /// <returns></returns>
        public DataSet GetAllCompanyMenuByCompanyUserID(string companyUserId)
        {
            DbParameter getPar = DBHelper.CreateInParam("@userid", (DbType)SqlDbType.Int, companyUserId);
            string commandText = "SELECT DISTINCT ";
            commandText += "[dbo].[zby_CA_CompanyModule].[Name] AS [CompanyModuleName], [dbo].[zby_CA_CompanyMenu].[MenuName], [dbo].[zby_CA_CompanyModule].[CompanyModuleOrder],";
            commandText += "[dbo].[zby_CA_CompanyMenu].[CompanyModuleID], [dbo].[zby_CA_CompanyMenu].[MenuOrder], [dbo].[zby_CA_CompanyMenu].[MenuUrl],[dbo].[zby_CA_CompanyMenu].[LableName],";
            commandText += "[dbo].[zby_CA_CompanyMenu].[LableUrl],[dbo].[zby_CA_CompanyMenu].[id] as [menuid],[dbo].[zby_CA_CompanyModule].[id] as [moduleid]";
            commandText += "FROM ";
            commandText += "[dbo].[zby_CA_CompanyUserRoles] INNER JOIN ";
            commandText += "[dbo].[zby_CA_CompanyUser] ON [dbo].[zby_CA_CompanyUserRoles].[CompanyUserId] = [dbo].[zby_CA_CompanyUser].[ID] INNER JOIN  ";
            commandText += "[dbo].[zby_CA_CompanyRoles] ON [dbo].[zby_CA_CompanyUserRoles].[CompanyRolesId] = [dbo].[zby_CA_CompanyRoles].[ID] INNER JOIN ";
            commandText += "[dbo].[zby_CA_CompanyRolesCompanyModule] ON [dbo].[zby_CA_CompanyRoles].[ID] = [dbo].[zby_CA_CompanyRolesCompanyModule].[RolesID] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyModule] ON [dbo].[zby_CA_CompanyRolesCompanyModule].[CompanyModuleID] = [dbo].[zby_CA_CompanyModule].[ID] INNER JOIN ";
            commandText += "[dbo].[zby_CA_CompanyMenu] ON [dbo].[zby_CA_CompanyModule].[ID] = [dbo].[zby_CA_CompanyMenu].[CompanyModuleID]";
            commandText += "WHERE ([dbo].[zby_CA_CompanyUser].[ID] = @userid)";
            commandText += "ORDER BY [dbo].[zby_CA_CompanyModule].[CompanyModuleOrder] ASC";
            return DBHelper.ExecuteDataSet(CommandType.Text,commandText,getPar);

        }
        /// <summary>
        /// 根據取得所有菜單信息
        /// </summary>
        /// <param name="companyUserId"></param>
        /// <returns></returns>
        public DataSet GetAllCompanyMenu()
        {
            string commandText = "SELECT DISTINCT ";
            commandText += "[dbo].[zby_CA_CompanyModule].[Name] AS [CompanyModuleName], [dbo].[zby_CA_CompanyMenu].[MenuName], [dbo].[zby_CA_CompanyModule].[CompanyModuleOrder],";
            commandText += "[dbo].[zby_CA_CompanyMenu].[CompanyModuleID], [dbo].[zby_CA_CompanyMenu].[MenuOrder], [dbo].[zby_CA_CompanyMenu].[MenuUrl],[dbo].[zby_CA_CompanyMenu].[LableName],";
            commandText += "[dbo].[zby_CA_CompanyMenu].[LableUrl],[dbo].[zby_CA_CompanyMenu].[id] as [menuid],[dbo].[zby_CA_CompanyModule].[id] as [moduleid]";
            commandText += "FROM ";
            commandText += "[dbo].[zby_CA_CompanyModule] INNER JOIN  [dbo].[zby_CA_CompanyMenu] ON [dbo].[zby_CA_CompanyModule].[ID] = [dbo].[zby_CA_CompanyMenu].[CompanyModuleID]  ";
            commandText += "ORDER BY [dbo].[zby_CA_CompanyModule].[CompanyModuleOrder] ASC  ";
            return DBHelper.ExecuteDataSet(CommandType.Text, commandText);
        }
        /// <summary>
        /// 根據ID取得菜單信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataSet GetCompanyMenuByCompanyMenuId(string id)
        {
            DbParameter getPar = DBHelper.CreateInParam("@id", (DbType)SqlDbType.Int, id);
            string commandText = "SELECT * FROM [dbo].[zby_CA_CompanyMenu] WHERE [dbo].[zby_CA_CompanyMenu].[ID]=@id ";
            return DBHelper.ExecuteDataSet(CommandType.Text, commandText, getPar);
        }
       
        /// <summary>
        /// 根據ID更新MenuName MenuOrder MenuUrl lablename lableurl三個字段
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="order"></param>
        /// <param name="url"></param>
        /// <returns></returns>
        public int UpDateCompanyMenuByCompanyMenuId(MenuInfo mi)
        {
            DbParameter[] getPar ={
                                  DBHelper.CreateInParam("@id", (DbType)SqlDbType.Int, mi.ID),
                                  DBHelper.CreateInParam("@menuname", (DbType)SqlDbType.NVarChar,mi.Name),
                                  DBHelper.CreateInParam("@lablename", (DbType)SqlDbType.NVarChar,mi.LableName),
                                  DBHelper.CreateInParam("@menuorder", (DbType)SqlDbType.TinyInt,mi.Order),
                                  DBHelper.CreateInParam("@menuurl", (DbType)SqlDbType.NVarChar,mi.Url),
                                  DBHelper.CreateInParam("@pid",(DbType)SqlDbType.Int,mi.Pid),
                                  DBHelper.CreateInParam("@lableurl", (DbType)SqlDbType.NVarChar,mi.LableUrl)
                                  };
            string commandText = "UPDATE [dbo].[zby_CA_CompanyMenu] SET [PID]=@pid,[MenuName]=@menuname,[MenuOrder]=@menuorder,[MenuUrl] =@menuurl,[LableName]=@lablename,[LableUrl]=@lableurl WHERE [id]=@id";
            return DBHelper.ExecuteNonQuery(CommandType.Text,commandText,getPar);
        }
        /// <summary>
        /// 根據ID取得模塊信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataSet GetCompanyModuleById(string id)
        {
            DbParameter getPar = DBHelper.CreateInParam("@id", (DbType)SqlDbType.Int, id);
            string commandText = "SELECT * FROM [dbo].[zby_CA_CompanyModule] WHERE [dbo].[zby_CA_CompanyModule].[ID]=@id ";
            return DBHelper.ExecuteDataSet(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據ID更新 Name Order字段
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public int UpDateCompanyModuleNameOrderById(string id, string name, string order)
        {
            DbParameter[] getPar ={
                                  DBHelper.CreateInParam("@id", (DbType)SqlDbType.Int,  id),
                                  DBHelper.CreateInParam("@name", (DbType)SqlDbType.NVarChar,name),
                                  DBHelper.CreateInParam("@order", (DbType)SqlDbType.TinyInt, order)                                 
                                  };
            string commandText = "UPDATE [dbo].[zby_CA_CompanyModule] SET[Name]=@name,[moduleOrder]=@order WHERE [id]=@id";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 添加菜單
        /// </summary>
        /// <param name="mi"></param>
        /// <returns></returns>
        public int AddCompanyMenu(MenuInfo mi)
        {
            DbParameter[] getPar ={
                                  DBHelper.CreateInParam("@menuname", (DbType)SqlDbType.NVarChar, mi.Name),
                                  DBHelper.CreateInParam("@moduleid", (DbType)SqlDbType.Int,mi.CompanyModuleId),
                                  DBHelper.CreateInParam("@lablename", (DbType)SqlDbType.NVarChar, mi.LableName),
                                  DBHelper.CreateInParam("@menuorder", (DbType)SqlDbType.TinyInt,mi.Order),
                                  DBHelper.CreateInParam("@menuurl", (DbType)SqlDbType.NVarChar,mi.Url),
                                  DBHelper.CreateInParam("@lableurl", (DbType)SqlDbType.NVarChar, mi.LableUrl),
                                  DBHelper.CreateInParam("@pid",(DbType)SqlDbType.Int,mi.Pid),
                                  DBHelper.CreateInParam("@time", (DbType)SqlDbType.DateTime, mi.Time)
                                  };
            string commandText = "INSERT INTO [dbo].[zby_CA_CompanyMenu]";
            commandText+="([PID],[CompanyModuleID],[MenuName],[MenuOrder],[MenuUrl],[LableName],[LableUrl],[MenuTimes])";
            commandText += "VALUES(@pid,@moduleid,@menuname,@menuorder,@menuurl,@lablename,@lableurl,@time)";
            return DBHelper.ExecuteNonQuery(CommandType.Text,commandText,getPar);
        }
        /// <summary>
        /// 刪除菜單
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelCompanyMenu(string id)
        {
            DbParameter getPar =DBHelper.CreateInParam("@id", (DbType)SqlDbType.Int,id);
            string commandText = "DELETE FROM [dbo].[zby_CA_CompanyMenu] WHERE [id]=@id";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
    }
}
